﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Data.Models;
using System.Collections.Generic;
using AccessControl.Contracts.Models;
using Action = AccessControl.Contracts.Models.Action;

namespace AccessControl.DataManagers.Tables.Managers
{
    /// <summary>
    /// Manages the operators in the DB
    /// </summary>
    class OperatorsManager : TableManager<IOperator>
    {
        #region Fields

        private static OperatorsManager s_Instance = null;
        private SqlDataAdapter _operatorsAdapter, _operatorActionsAdapter;

		private static readonly string COL_1_ID = "ID";
		private static readonly string COL_2_NAME = "NAME";
		private static readonly string COL_3_PASSWORD = "PASSWORD";

		private static readonly string COL_1_OPERAOTR_ID = "OPERATOR_ID";
		private static readonly string COL_2_ACTION_ID = "ACTION_ID";

        #endregion

        #region Private Constructor

        /// <summary>
        /// Prevents a default instance of the <see cref="OperatorsManager"/> class from being created.
        /// </summary>
        private OperatorsManager()
        {
        }

        #endregion

        #region Public Properties

        /// <summary>
        /// Gets the instance.
        /// </summary>
        public static OperatorsManager Instance
        {
            get
            {
                lock (typeof(OperatorsManager))
                {
                    if (s_Instance == null)
                        s_Instance = new OperatorsManager();

                    return s_Instance;
                }
            }
        }

        #endregion

        #region TableManager Overrides

        /// <summary>
        /// Creates using the specified connection.
        /// </summary>
        /// <param name="connection">The connection.</param>
        public override void Create(SqlConnection connection)
        {
            /* Create the operators table */
            SqlCommand command = connection.CreateCommand();
            command.CommandText = string.Format("CREATE TABLE {0} ({1} INT, {2} TEXT, {3} TEXT)", 
												TablesNames.OPERATORS, COL_1_ID, COL_2_NAME, COL_3_PASSWORD);
            command.ExecuteNonQuery();

            /* Add the default user */
            command = connection.CreateCommand();
            command.CommandText = string.Format("INSERT INTO {0} VALUES (@{1}, @{2}, @{3})", TablesNames.OPERATORS, COL_1_ID, COL_2_NAME, COL_3_PASSWORD);
            command.Parameters.Add(new SqlParameter(COL_1_ID, (int)1));
            command.Parameters.Add(new SqlParameter(COL_2_NAME, "admin"));
            command.Parameters.Add(new SqlParameter(COL_3_PASSWORD, string.Empty));
            command.ExecuteNonQuery();

            /* Create the operators actions table */
            command = connection.CreateCommand();
            command.CommandText = string.Format("CREATE TABLE {0} ({1} INT, {2} INT)", TablesNames.OPERATORS_ACTIONS, COL_1_OPERAOTR_ID, COL_2_ACTION_ID);
            command.ExecuteNonQuery();

            /* And by default the admin can do everything */
            foreach (Action action in Enum.GetValues(typeof(Action)))
            {
                command = connection.CreateCommand();
                command.CommandText = string.Format("INSERT INTO {0} VALUES (@{1}, @{2})", TablesNames.OPERATORS_ACTIONS, COL_1_OPERAOTR_ID, COL_2_ACTION_ID);
                command.Parameters.Add(new SqlParameter(COL_1_OPERAOTR_ID, (int)1));
                command.Parameters.Add(new SqlParameter(COL_2_ACTION_ID, (int)action));
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Initializes this instance.
        /// </summary>
        public override void Initialize(SqlConnection connection)
        {
			/* Create the adapters */
			_operatorsAdapter = new SqlDataAdapter();
			_operatorActionsAdapter = new SqlDataAdapter();

			/* Fill the commands */
			_operatorsAdapter.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TablesNames.OPERATORS), connection);
			_operatorsAdapter.InsertCommand = new SqlCommand(string.Format("INSERT INTO {0} ({1}, {2}, {3}) VALUES (@{1}, @{2}, @{3})",
															TablesNames.OPERATORS, COL_1_ID, COL_2_NAME, COL_3_PASSWORD), connection);
			_operatorsAdapter.UpdateCommand = new SqlCommand(string.Format("UPDATE {0} SET {2} = @{2}, {3} = @{3} WHERE {1} = @{1}",
															TablesNames.OPERATORS, COL_1_ID, COL_2_NAME, COL_3_PASSWORD), connection);
			_operatorsAdapter.DeleteCommand = new SqlCommand(string.Format("DELETE FROM {0} WHERE {1} = @{1}",
															TablesNames.OPERATORS, COL_1_ID), connection);

			_operatorActionsAdapter.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TablesNames.OPERATORS_ACTIONS), connection);
			_operatorActionsAdapter.InsertCommand = new SqlCommand(string.Format("INSERT INTO {0} ({1}, {2}) VALUES (@{1}, @{2})",
																   TablesNames.OPERATORS_ACTIONS, COL_1_OPERAOTR_ID, COL_2_ACTION_ID), connection);
			_operatorActionsAdapter.DeleteCommand = new SqlCommand(string.Format("DELETE FROM {0} WHERE {1} = @{1}",
																   TablesNames.OPERATORS_ACTIONS, COL_1_OPERAOTR_ID), connection);
        } 

        /// <summary>
        /// Gets the list.
        /// </summary>
        /// <returns></returns>
        public override bool FillList(IList<IOperator> items)
        {
            Operator myOperator;

			/* First fill the operators */
			using (SqlDataReader operatorsReader = _operatorsAdapter.SelectCommand.ExecuteReader())
			{
				while (operatorsReader.Read())
				{
					myOperator = new Operator()
					{
						Id = operatorsReader.GetInt32(0),
						UserName = operatorsReader.GetString(1),
						Password = operatorsReader.GetString(2)
					};

					/* Add to the list */
					items.Add(myOperator);
				}
			}

			/* Now fill the actions for that operator */
			using (SqlDataReader actionsReader = _operatorActionsAdapter.SelectCommand.ExecuteReader())
			{
				while (actionsReader.Read())
				{
					foreach (IOperator oper in items)
					{
						if (actionsReader.GetInt32(0) == oper.Id)
							oper.AllowedActions.Add((Action)actionsReader.GetInt32(1));
					}
				}
			}
			
			return true;
        }

        /// <summary>
        /// Adds the specified item.
        /// </summary>
        /// <param name="item">The item.</param>
		public override bool Add(IOperator item)
        {
			/* First add the operator */
			_operatorsAdapter.InsertCommand.Parameters.Clear();

			/* Set the item id */
			item.Id = GetHighestId(_operatorsAdapter.SelectCommand.Connection, TablesNames.OPERATORS, COL_1_ID) + 1;

			_operatorsAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));
			_operatorsAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_2_NAME, item.UserName));
			_operatorsAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_3_PASSWORD, item.Password));
			_operatorsAdapter.InsertCommand.ExecuteNonQuery();

			/* And add it's actions */
			foreach (Action action in item.AllowedActions)
			{
				_operatorActionsAdapter.InsertCommand.Parameters.Clear();
				_operatorActionsAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_1_OPERAOTR_ID, item.Id));
				_operatorActionsAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_2_ACTION_ID, (int)action));
				_operatorActionsAdapter.InsertCommand.ExecuteNonQuery();
			}

			return true;
		}

        /// <summary>
        /// Updates the specified item.
        /// </summary>
        /// <param name="item">The item.</param>
		public override bool Update(IOperator item)
        {
			/* First update the operator */
			_operatorsAdapter.UpdateCommand.Parameters.Clear();
			_operatorsAdapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));
			_operatorsAdapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_2_NAME, item.UserName));
			_operatorsAdapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_3_PASSWORD, item.Password));
			_operatorsAdapter.UpdateCommand.ExecuteNonQuery();

			/* Now delete all the actions for this operator */
			_operatorActionsAdapter.DeleteCommand.Parameters.Clear();
			_operatorActionsAdapter.DeleteCommand.Parameters.Add(new SqlParameter(COL_1_OPERAOTR_ID, item.Id));
			_operatorActionsAdapter.DeleteCommand.ExecuteNonQuery();

			/* And add */
			foreach (Action action in item.AllowedActions)
			{
				_operatorActionsAdapter.InsertCommand.Parameters.Clear();
				_operatorActionsAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_1_OPERAOTR_ID, item.Id));
				_operatorActionsAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_2_ACTION_ID, (int)action));
				_operatorActionsAdapter.InsertCommand.ExecuteNonQuery();
			}

			return true;
        }

        /// <summary>
        /// Deletes the specified item.
        /// </summary>
        /// <param name="item">The item.</param>
		public override bool Delete(IOperator item)
        {
			_operatorsAdapter.DeleteCommand.Parameters.Clear();
			_operatorsAdapter.DeleteCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));
			_operatorsAdapter.DeleteCommand.ExecuteNonQuery();

			_operatorActionsAdapter.DeleteCommand.Parameters.Clear();
			_operatorActionsAdapter.DeleteCommand.Parameters.Add(new SqlParameter(COL_1_OPERAOTR_ID, item.Id));
			_operatorActionsAdapter.DeleteCommand.ExecuteNonQuery();

			return true;
		}

        #endregion
    }
}
